###############################################################################
# AZ cleaning
#
# written may 8 2024
#   sbaltz at mit
###############################################################################

import pandas as pd

RAW_FILE_NAME = '../transcribed/2020_general_maricopa_hand_count.csv'
SAVE_FILE_NAME = '../ready/maricopa_cleaned.csv'
COUNTY_NAME = 'MARICOPA'

ct = pd.read_csv(RAW_FILE_NAME)

cleaned = pd.DataFrame()

for i in range(len(ct)):
    if 'Site: ' in str(ct['candidate'][i]):
        precinct = str(ct['candidate'][i].replace('Site: ',''))
        precinct = precinct.replace('Ballots Cast:','')
        mode = 'ELECTION DAY'
    if 'EARLY VOTING' in str(ct['candidate'][i]):
        precinct = ''
        mode = 'EARLY'
    if 'Race: ' in str(ct['office'][i]):
        office = str(ct['office'][i].replace('Race: ',''))
    if str(ct["original"][i]) != "nan" and str(ct["audited"][i]) != "nan":
        try:
            state = "ARIZONA"
            county = COUNTY_NAME,
            candidate = str(ct['candidate'][i])
            original = int(ct['original'][i])
            audited = int(ct['audited'][i])
            difference = audited - original
            method = "MANUAL"
            if 'TOTAL' not in candidate:
                cleaned = pd.concat([cleaned,
                            pd.DataFrame([[state,county,precinct,office,\
                            candidate,original,audited,difference,mode,method]])
                            ])
        except Exception as inst:
            print(f"ROW {i} NOT SAVED: ", inst)

cleaned.columns = ['state',
                   'county',
                   'precinct',
                   'office',
                   'candidate',
                   'original',
                   'audited',
                   'difference',
                   'mode',
                   'type'
                  ]

cleaned.precinct = cleaned.precinct.str.strip()
cleaned.office = cleaned.office.str.strip()
cleaned.county = COUNTY_NAME

cleaned.to_csv(SAVE_FILE_NAME, index=False)
